package com.animal.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;

import com.animal.bean.DailyBean;

public class DailyManage {

	public static int selectNum(){
		int n=0;
		Connection con =  Connect.getCon();
		Statement stat =null;
		ResultSet rs = null;
		try{
			stat = con.createStatement();
			String sql = "select count(*) from animalfile09_dailyarchive";
			rs = stat.executeQuery(sql);
			while(rs.next()){
				n = rs.getInt(1);
			}
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("查询数据出错!");
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			System.out.println("select数据库成功获取!");
			Connect.close2(con, stat);
		}
		return n;
	}
	public static boolean updateState(String FileNumber,String FileState){
		boolean issuc = false;
		Connection con =  Connect.getCon();
		Statement stat =null;
		try{
			stat = con.createStatement();
			String sql = "update animalfile09_dailyarchive set FileState='"+FileState+"' where FileNumber = '"+FileNumber+"'";
			int count=stat.executeUpdate(sql);
			if(count>0)
				issuc = true;
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("更新数据出错!");
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			System.out.println("update数据库成功获取!");
			Connect.close2(con, stat);
		}
		return issuc;
	}
	
	public static boolean delete(String FileNumber){
		boolean issuc = false;
		Connection con =  Connect.getCon();
		Statement stat =null;
		try{
			stat = con.createStatement();
			String sql = "delete from animalfile09_dailyarchive where FileNumber = '"+FileNumber+"'";
			int count=stat.executeUpdate(sql);
			if(count>0)
				issuc = true;	
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("删除数据出错!");
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			System.out.println("delete数据库成功获取!");
			Connect.close2(con, stat);
		}
		return issuc;
	}
	
	public static Collection selectAll(){
		Collection list = new ArrayList();
		Connection con =  Connect.getCon();
		Statement stat =null;
		ResultSet rs = null;
		try{
			stat = con.createStatement();
			String sql = "select * from animalfile09_dailyarchive";
			rs = stat.executeQuery(sql);

			while(rs.next()){
				
				String FileNumber = (rs.getString("FileNumber")==null) ? "null" : rs.getString("FileNumber");
				String FileTitle = (rs.getString("FileTitle")==null) ? "null" : rs.getString("FileTitle");
				String UserID = (rs.getString("UserID")==null) ? "null" : rs.getString("UserID");
				String AnimalID = (rs.getString("AnimalID")==null) ? "null" : rs.getString("AnimalID");
				//Timestamp DailyerimentDate = (rs.getTimestamp("DailyerimentDate").equals(null)) ? new Timestamp(System.currentTimeMillis()):rs.getTimestamp("DailyerimentDate");
				//Timestamp FillingDate = (rs.getTimestamp("FillingDate")==null) ? Timestamp.valueOf("2000-00-00 00:00:00"):rs.getTimestamp("FillingDate");
				String FileState = (rs.getString("FileState")==null) ? "null" : rs.getString("FileState");
				
				Timestamp FillingDate = rs.getTimestamp("FillingDate");

				DailyBean daily = new DailyBean();
				daily.setFileNumber(FileNumber);
				daily.setFileTitle(FileTitle);
				daily.setUserID(UserID);
				daily.setAnimalID(AnimalID);
				daily.setFillingDate(FillingDate);
				daily.setFileState(FileState);
/*				
				Date d;
				Time t;
				d = rs.getDate("DailyerimentDate");
				t = rs.getTime("DailyerimentDate");
				Daily.setDailyerimentDate(d.toString()+""+t.toString());
				d = rs.getDate("FillingDate");
				t = rs.getTime("FillingDate");
				Daily.setFillingDate(d.toString()+""+t.toString());
*/
				
				list.add(daily);
			}
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("查询信息出错！");
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			System.out.println("数据库成功获取！");
			Connect.close3(rs, con, stat);
		}
		return list;
	}
	
	//涛
	public static Collection selectLike(String filenumber,String filetitle,String userid,String animalid){
		Collection list = new ArrayList();
		Connection con =  Connect.getCon();
		Statement stat =null;
		ResultSet rs = null;
		try{
			stat = con.createStatement();
			String sql =  "select * from animalfile09_dailyarchive where FileNumber like '%"+filenumber+"%' and FileTitle like '%"
						+filetitle+"%' and UserID like '%"+userid+"%' and AnimalID like '%"+animalid+"%'";
			rs = stat.executeQuery(sql);

			while(rs.next()){
				
				String FileNumber = (rs.getString("FileNumber")==null) ? "null" : rs.getString("FileNumber");
				String FileTitle = (rs.getString("FileTitle")==null) ? "null" : rs.getString("FileTitle");
				String UserID = (rs.getString("UserID")==null) ? "null" : rs.getString("UserID");
				String AnimalID = (rs.getString("AnimalID")==null) ? "null" : rs.getString("AnimalID");
				//Timestamp DailyerimentDate = (rs.getTimestamp("DailyerimentDate").equals(null)) ? new Timestamp(System.currentTimeMillis()):rs.getTimestamp("DailyerimentDate");
				//Timestamp FillingDate = (rs.getTimestamp("FillingDate")==null) ? Timestamp.valueOf("2000-00-00 00:00:00"):rs.getTimestamp("FillingDate");
				String FileState = (rs.getString("FileState")==null) ? "null" : rs.getString("FileState");
				
				Timestamp FillingDate = rs.getTimestamp("FillingDate");

				DailyBean daily = new DailyBean();
				daily.setFileNumber(FileNumber);
				daily.setFileTitle(FileTitle);
				daily.setUserID(UserID);
				daily.setAnimalID(AnimalID);
				daily.setFillingDate(FillingDate);
				daily.setFileState(FileState);
/*				
				Date d;
				Time t;
				d = rs.getDate("DailyerimentDate");
				t = rs.getTime("DailyerimentDate");
				Daily.setDailyerimentDate(d.toString()+""+t.toString());
				d = rs.getDate("FillingDate");
				t = rs.getTime("FillingDate");
				Daily.setFillingDate(d.toString()+""+t.toString());
*/
				
				list.add(daily);
			}
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("查询信息出错！");
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			System.out.println("数据库成功获取！");
			Connect.close3(rs, con, stat);
		}
		return list;
	}
	
	//于
	public static Collection selectAllof(int startIndex){
		Collection list = new ArrayList();
		Connection con =  Connect.getCon();
		PreparedStatement pre = null;

		ResultSet rs = null;

		try{
			String sql = "select  animalfile09_dailyarchive.*,animalfile09_systemuser.Name from  animalfile09_dailyarchive LEFT JOIN animalfile09_systemuser on  animalfile09_systemuser.UserID=animalfile09_dailyarchive.UserID ORDER BY `FillingDate` DESC LIMIT ?,?";

			pre=con.prepareStatement(sql);

			pre.setInt(1,startIndex);
			pre.setInt(2,10);
			rs = pre.executeQuery();
			while(rs.next()){

				String FileNumber = (rs.getString("FileNumber")==null) ? "null" : rs.getString("FileNumber");
				String FileTitle = (rs.getString("FileTitle")==null) ? "null" : rs.getString("FileTitle");
				String UserID = (rs.getString("UserID")==null) ? "null" : rs.getString("UserID");
				String AnimalID = (rs.getString("AnimalID")==null) ? "null" : rs.getString("AnimalID");
				String FileState = (rs.getString("FileState")==null) ? "未审核" : rs.getString("FileState");
				Timestamp FillingDate = rs.getTimestamp("FillingDate");
				String DailyDataTip = (rs.getString("DailyDataTip")==null) ? "无" : rs.getString("DailyDataTip");
				String UserName=(rs.getString("Name")==null) ? "无" : rs.getString("Name");;


				DailyBean d = new DailyBean();
				d.setFileNumber(FileNumber);
				d.setFileTitle(FileTitle);
				d.setUserID(UserID);
				d.setAnimalID(AnimalID);
				d.setFillingDate(FillingDate);
				d.setFileState(FileState);
				d.setDailyDataTip(DailyDataTip);

				d.setUserName(UserName);
				//System.out.println(UserID);
				list.add(d);
			}


		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("查询信息出错！");
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			System.out.println("数据库成功获取！");
			Connect.close3(rs, con, pre);
		}
		return list;
	}
	@SuppressWarnings("finally")
	public static int counts() {
		Connection con =  Connect.getCon();
		Statement stat =null;
		ResultSet rs = null;
		int res = 0;
		try{
			stat = con.createStatement();
			String sql = "select count(FileNumber) from animalfile09_dailyarchive";
			rs = stat.executeQuery(sql);

			while(rs.next()){
				 res =Integer.valueOf(rs.getInt("count(FileNumber)"));
			}
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("查询信息出错！");
		}catch(Exception e){
			e.printStackTrace();
		}
		finally {
			return res;
		}
	}


}